const express = require('express');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const router = express.Router();
const db = require('../db');
const { Op } = require('sequelize'); // 确保在顶部引入 Sequelize 的 Op 对象

router.post('/login', async (req, res) => {
  console.log('Received request body:', req.body);
  const { realname, password } = req.body;

  try {
    // 查询用户
    const queryResult = await db.query(
      'SELECT * FROM USERS WHERE REALNAME = :realname',
      {
        replacements: { realname },
        type: db.QueryTypes.SELECT
      }
    );

    const users = Array.isArray(queryResult) ? queryResult : [queryResult];
    console.log('查询结果:', users);

    if (users.length === 0) {
      return res.status(401).json({ error: '用户不存在或密码错误' });
    }

    const user = users[0];
    console.log('用户对象:', user);

    // 验证密码
    const isMatch = await bcrypt.compare(password, user.PASSWORD);
    if (!isMatch) {
      return res.status(401).json({ error: '用户不存在或密码错误' });
    }

    // 生成 JWT Token
    const secret = process.env.JWT_SECRET; // 确保环境变量已设置
    console.log('JWT_SECRET:', secret); // 调试日志
    const token = jwt.sign(
      { userId: user.USER_ID },
      secret,
      { expiresIn: '1h' }
    );

    res.json({
      token,
      user: {
        id: user.USER_ID,
        realname: user.REALNAME
      }
    });
  } catch (error) {
    console.error('登录失败:', error);
    res.status(500).json({ error: error.message });
  }
});

// 获取科室列表
router.get('/getDeptList', async (req, res) => {
  try {
    // 返回所有列
    const queryResult = await db.query(
      'SELECT * FROM DEPT', // 返回所有字段
      {
        type: db.QueryTypes.SELECT 
      }
    );
    // 直接返回完整的对象数组
    const deptList = queryResult;
    res.json({
      code: 200,
      data: deptList, // 返回完整的科室对象数组
      message: '科室列表获取成功'
    });
  } catch (error) {
    console.error('获取科室列表失败:', error);
    res.status(500).json({
      code: 500,
      message: '获取科室列表失败，请稍后再试'
    });
  }
});

// 获取已完成闭环列表
router.get('/getCloseOkList', async (req, res) => {
  try {
    // 修改 SQL 查询，返回所有列（*）
    const queryResult = await db.query(
      'SELECT * FROM CLOSE_LOOP', // 返回所有字段
      {
        type: db.QueryTypes.SELECT // 如果使用 Sequelize 需要指定
      }
    );
    // 直接返回完整的对象数组，不需要 map 转换
    const closeLoopList = queryResult;
    res.json({
      code: 200,
      data: closeLoopList, // 返回完整的闭环对象数组
      message: '闭环列表获取成功'
    });
  } catch (error) {
    console.error('获取闭环列表失败:', error);
    res.status(500).json({
      code: 500,
      message: '获取闭环列表失败，请稍后再试'
    });
  }
});

// 查询患者列表
router.get('/getInOutHospList', async (req, res) => {
  try {
    // 解析参数（移除分页参数）
    const {
      closeLoopId,
      deptId,
      patName,
      startTime,
      endTime,
      sortField = 'INP_TIME',
      sortOrder = 'DESC'
    } = req.query;

    // 白名单验证排序字段
    const allowedSortFields = ['PAT_NAME', 'INP_TIME', 'DEPT_ID', 'PAT_ID'];
    if (!allowedSortFields.includes(sortField)) {
      sortField = 'INP_TIME';
    }

    // 固定分页参数（默认每页 20 条）
    const pageSizeInt = 20;

    // 构建基础查询条件
    let query = 'SELECT a.* FROM pat_view a';
    let joinClause = '';
    const whereConditions = { PAT_STATE: '3' }; // 固定条件

    // 初始化替换参数对象
    const replacements = {};

    // 获取动态表名 VIEW_NAME（如果存在 closeLoopId）
    let viewName = null;
    if (closeLoopId) {
      // 1. 先查询对应的 VIEW_NAME
      const viewResult = await db.query(
        'SELECT VIEW_NAME FROM CLOSE_LOOP_DOCTOR_ADVICE WHERE CLOSE_LOOP_ID = :closeLoopId',
        {
          replacements: { closeLoopId },
          type: db.QueryTypes.SELECT
        }
      );


      // //防止SQL注入，确保返回的结果是一个数组
      // if (viewResult.length > 0) {
      //   viewName = viewResult[0].VIEW_NAME;
      //   // 2. 白名单验证 VIEW_NAME 的安全性（避免 SQL 注入）
      //   const allowedViewNames = ['PAT_VIEW', 'CLOSE_LOOP_DOCTOR_ADVICE','DEPT']; // 根据实际业务定义
      //   if (!allowedViewNames.includes(viewName)) {
      //     throw new Error('Invalid view name');
      //   }
      // }
    }

    // 3. 构建动态 JOIN 条件（使用 VIEW_NAME）
    if (viewName) {
      joinClause = `INNER JOIN (SELECT PAT_ID FROM ${viewName} WHERE CLOSE_LOOP_ID = :closeLoopId) B ON a.PAT_ID = B.PAT_ID`;
      // 将 closeLoopId 添加到替换参数
      replacements.closeLoopId = closeLoopId;
    }

    // 处理动态WHERE条件
    if (deptId) {
      whereConditions.DEPT_ID = deptId;
    }

    if (startTime && endTime) {
      whereConditions.INP_TIME = {
        [Op.between]: [startTime, endTime]
      };
    }

    if (patName) {
      whereConditions.PAT_NAME = {
        [Op.like]: `%${patName}%`
      };
    }

    // 构建基础查询语句
    const baseQuery = `
      ${query}
      ${joinClause ? joinClause : ''}
      WHERE 1=1
    `;

    // 构建条件字符串和替换参数
    const conditions = [];
    Object.entries(whereConditions).forEach(([key, value]) => {
      if (typeof value === 'object') {
        if (value[Op.like]) {
          conditions.push(`${key} LIKE :${key}`);
          replacements[key] = value[Op.like];
        } else if (value[Op.between]) {
          const [start, end] = value[Op.between];
          conditions.push(`(${key} BETWEEN :${key}Start AND :${key}End)`);
          replacements[`${key}Start`] = start;
          replacements[`${key}End`] = end;
        }
      } else {
        conditions.push(`${key} = :${key}`);
        replacements[key] = value;
      }
    });

    // 构建计数查询语句
    const countQuery = `
      SELECT COUNT(*) AS total 
      FROM pat_view a 
      ${joinClause ? joinClause : ''}
      WHERE 1=1 ${conditions.length > 0 ? 'AND ' + conditions.join(' AND ') : ''}
    `;

    // 执行计数查询
    const countResult = await db.query(countQuery, {
      replacements: replacements,
      type: db.QueryTypes.SELECT
    });
    const total = countResult[0].total;

    // 根据总条数决定是否分页
    let result;
    if (total > pageSizeInt) {
      // 自动分页（显示第一页）
      const pageQuery = `
        SELECT * FROM (
          SELECT 
            a.*, 
            ROWNUM AS rn 
          FROM (
            ${baseQuery} 
            ${conditions.length > 0 ? 'AND ' + conditions.join(' AND ') : ''}
            ORDER BY ${sortField} ${sortOrder}
          ) a
          WHERE ROWNUM <= ${pageSizeInt}
        ) 
        WHERE rn >= 1
      `;

      result = await db.query(pageQuery, {
        replacements: replacements,
        type: db.QueryTypes.SELECT
      });
    } else {
      // 不分页，直接返回所有数据
      const pageQuery = `
        ${baseQuery} 
        ${conditions.length > 0 ? 'AND ' + conditions.join(' AND ') : ''}
        ORDER BY ${sortField} ${sortOrder}
      `;

      result = await db.query(pageQuery, {
        replacements: replacements,
        type: db.QueryTypes.SELECT
      });
    }

    // 返回结果
    res.json({
      code: 200,
      data: {
        list: result,
        total: total,
        page: 1, // 默认第一页
        pageSize: total > pageSizeInt ? pageSizeInt : total,
        sort: {
          field: sortField,
          order: sortOrder
        }
      },
      message: '获取住院患者列表成功'
    });
  } catch (error) {
    console.error('获取住院患者列表失败:', error);
    res.status(500).json({
      code: 500,
      message: '获取住院患者列表失败，请稍后再试'
    });
  }
});





module.exports = router;